package bean;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class guest_bookDTO {
	
	private guest_bookVO vo;
	private Connection conn;

	int list_size = 5; // 1페이지당 표시 목록수
	int block_size = 5; // 1블럭당 표시되는 페이지 수
	int tot_size = 0; // 총 계시물 수
	int tot_page = 0; // 총 페이지 수
	int tot_block =0; // 총 블럭 수
	int now_page = 1; // 현재 표시되는 페이지
	int now_block = 1; // 현재 표시되는 블럭
	int start_number=0; // 현재 페이지의 시작 게시물 번호
	int end_number=0; // 현재 페이지의 끝 게시물 번호
	int start_page; // 현재 블럭에서 표시되는 페이지 번호
	int end_page=0; // 현재 블럭에서 마지막으로 표시되는 페이지
	
	public guest_bookDTO()
	{
		conn = new MyOracle().getConn();
	}
	
	public void page(){
		String sql = "select count(*) cnt from guestbook";
		
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				tot_size =  rs.getInt("cnt");
				//전체 페이지 수 
				tot_page = (int)Math.ceil(tot_size *1.0/list_size);
				
				//전체 블럭수
				tot_block = (int)Math.ceil(tot_page*1.0/block_size);
				
				//현재 블럭
				now_block =(int)Math.ceil(now_page*1.0/block_size);
				
				end_page = now_block * block_size;
				start_page = end_page - block_size+1;
								
				// 게시물의 시작번호와 끝 번호
				end_number =  now_page*list_size;
				start_number = end_number - list_size+1;
				
				//end_page, end_number 보정값
				if(end_page > tot_page)
				{
					end_page = tot_page;
				}
				if(end_number > tot_size)
				{
					end_number = tot_size;
				}
				
			}

			System.out.println("<li> tot_size ="+tot_size);
			System.out.println("<li> tot_page ="+tot_page);
			System.out.println("<li> tot_block ="+tot_block);
			System.out.println("<li> start_page ="+start_page);
			System.out.println("<li> now_page ="+now_page);
			System.out.println("<li> end_page ="+end_page);
			System.out.println("<li> now_block ="+now_block);
			System.out.println("start_nubmer"+start_number);
			System.out.println("end_nubmer"+ end_number);
		} catch (Exception e) {
			e.printStackTrace();
		}		
				
	}
	
	public boolean guest_book_insert(guest_bookVO vo)
	{
		boolean b = false;
		try {
			String m_id = "";
			String g_text = " ";
			String g_date = "";
			int g_rno = 0;
			
			
			m_id = vo.getM_id();
			g_text = vo.getG_text();
			g_date = vo.getG_date();
			g_rno = vo.getG_rno();
			
			String sql = "insert into guestbook(m_id, g_text, g_date, g_rno) values (?, ?, sysdate, seq_guestbook__rno.nextval)";
			
			PreparedStatement pst = conn.prepareStatement(sql);
			
			pst.setString(1, m_id);
			pst.setString(2, g_text);
	
			int n = pst.executeUpdate(); // 수정된 행의 개수
			
			pst.close();
			conn.close();
			b= true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		
		return b;
	}
	

	public ArrayList<guest_bookVO> select_all()
	{
		page();
		ArrayList<guest_bookVO> al = new ArrayList<guest_bookVO>();
		String sql ="select * from (" +
					" select rownum rn, c.* from (" + 
					" select *from guestbook"+
					" order by g_rno desc)c" +
					" )b" +
					" where b.rn between ? and ? ";

		
		
//a파트 : 선택, b파트 : 나열, c파트 : 정렬 및 검색		
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, start_number);
			pst.setInt(2, end_number);
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				guest_bookVO v = new guest_bookVO();
				v.setM_id(rs.getString("m_id"));
				v.setG_text(rs.getString("g_text"));
				v.setG_date(rs.getString("g_date"));
				v.setG_rno(Integer.parseInt(rs.getString("g_rno")));
							
				al.add(v);
				System.out.println(v.getM_id());
			}
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return al;
	}

	public boolean delete()
	{
		boolean b = false;
		System.out.println("g_rno : "+vo.getG_rno());
		String sql = "delete from guestbook where g_rno =?";
		
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, vo.getG_rno());
			int cnt = pst.executeUpdate();
			if(cnt>0) b=true;
			
			pst.close();
			conn.close();
		} catch (Exception e) {
		}
		
		
		return b;
	}
	
	
	
	public guest_bookVO getVo() {
		return vo;
	}

	public void setVo(guest_bookVO vo) {
		this.vo = vo;
		this.now_page = vo.getNow_page();
		this.now_block = vo.getNow_block();
	}

	public int getList_size() {
		return list_size;
	}

	public void setList_size(int list_size) {
		this.list_size = list_size;
	}

	public int getBlock_size() {
		return block_size;
	}

	public void setBlock_size(int block_size) {
		this.block_size = block_size;
	}

	public int getTot_size() {
		return tot_size;
	}

	public void setTot_size(int tot_size) {
		this.tot_size = tot_size;
	}

	public int getTot_page() {
		return tot_page;
	}

	public void setTot_page(int tot_page) {
		this.tot_page = tot_page;
	}

	public int getTot_block() {
		return tot_block;
	}

	public void setTot_block(int tot_block) {
		this.tot_block = tot_block;
	}

	public int getNow_page() {
		return now_page;
	}

	public void setNow_page(int now_page) {
		this.now_page = now_page;
	}

	public int getNow_block() {
		return now_block;
	}

	public void setNow_block(int now_block) {
		this.now_block = now_block;
	}

	public int getStart_number() {
		return start_number;
	}

	public void setStart_number(int start_number) {
		this.start_number = start_number;
	}

	public int getEnd_number() {
		return end_number;
	}

	public void setEnd_number(int end_number) {
		this.end_number = end_number;
	}

	public int getStart_page() {
		return start_page;
	}

	public void setStart_page(int start_page) {
		this.start_page = start_page;
	}

	public int getEnd_page() {
		return end_page;
	}

	public void setEnd_page(int end_page) {
		this.end_page = end_page;
	}
	
	
}
